package com.eece496.Database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map;
import java.util.TreeMap;

import com.eece496.Helpers.CourseInfo;
import com.eece496.Helpers.StudentInfo;

public class Enrollment extends Database {

	// private int enrollmentID;
	private int studentID;
	private String courseNumber;
	private String sectionNumber;

	public Enrollment() {
		tableName = "Enrollment";
	}

	public Enrollment(int studentID, String courseNumber, String sectionNumber) {
		tableName = "Enrollment";

		this.studentID = studentID;
		this.courseNumber = courseNumber;
		this.sectionNumber = sectionNumber;

	}

	@Override
	public void Insert() throws SQLException {
		Statement stmt = connection.createStatement();

		String sqlString = "INSERT INTO " + tableName + " VALUES (" + studentID
				+ ", " + AddQuotes(courseNumber) + ", "
				+ AddQuotes(sectionNumber) + ")";

		stmt.executeUpdate(sqlString);
		System.out.println(sqlString);
	}

	@Override
	public void Delete(String primaryKey) throws SQLException {
		Statement stmt = connection.createStatement();

		String sqlString = "DELETE from " + tableName + " WHERE studentID = "
				+ studentID + " and courseNumber = " + AddQuotes(courseNumber);
		stmt.executeUpdate(sqlString);

	}

	@Override
	public void Display() throws SQLException {
		// TODO Auto-generated method stub

	}

	//Don't Delete-created by Steven Chow
	public TreeMap<String, String> GetEnrolledCourses(int studentID)
			throws SQLException {
		TreeMap<String, String> CourseList = new TreeMap<String, String>();

		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + tableName + " where studentID = "
				+ studentID;

		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next())// check if user exists in databse
		{
			this.courseNumber = rs.getString("courseNumber");
			this.sectionNumber = rs.getString("sectionNumber");
			CourseList.put(this.courseNumber, this.sectionNumber);
		}
		return CourseList;
	}

	//Dont Delete-created by Steven Chow
	public ArrayList<CourseInfo> GetStudentCourseInfo(int studentID)
			throws SQLException {
		ArrayList<CourseInfo> list = new ArrayList<CourseInfo>();
		
		Statement stmt = connection.createStatement();
		
		String sqlString = "select course.courseNumber, course.courseName, "
				+ "course.sectionNumber, course.termYear, course.term, course.instructor "
				+ "from course inner join "
				+ tableName
				+ " on course.courseNumber = enrollment.courseNumber "
				+ "and course.sectionNumber = enrollment.sectionNumber where enrollment.studentID = "
				+ studentID;

		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next())
		{
			CourseInfo info = new CourseInfo();

			info.courseNumber = rs.getString("courseNumber");
			info.courseName = rs.getString("courseName");
			info.sectionNumber = rs.getString("sectionNumber");
			info.termYear = rs.getInt("termYear");
			info.term = rs.getString("term");
			info.instructor = rs.getString("instructor");
			list.add(info);
		}

		return list;
	}
	
	public ArrayList<StudentInfo> GetAllStudentsEnrolled(String courseNumber, String sectionNumber) throws SQLException
	{
		
		ArrayList<StudentInfo> list = new ArrayList<StudentInfo>();
		
		Statement stmt = connection.createStatement();
		String sqlString = "select student.studentID, student.firstname, student.lastname, student.program, student.theyear from student inner join enrollment "
				+ "on student.studentID = enrollment.studentID where courseNumber = " + AddQuotes(courseNumber) + " and "
				+ " sectionNumber = " + AddQuotes(sectionNumber);
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while(rs.next())
		{
			StudentInfo info = new StudentInfo();
			
			info.studentID = rs.getInt("studentID");
			info.firstname = rs.getString("firstName");
			info.lastname = rs.getString("lastName");
			info.program = rs.getString("program");
			info.year = rs.getInt("theYear");
			list.add(info);
		}
		return list;
	}
	
	//public 
	//Don't delete-created by Steven Chow
	public void DeleteCourseEnrollment(String courseNumber, String sectionNumber) throws SQLException
	{
		Statement stmt = connection.createStatement();

		String sqlString = "DELETE from " + tableName + " WHERE courseNumber = "
				+ AddQuotes(courseNumber) + " and sectionNumber = " + AddQuotes(sectionNumber);
		stmt.executeUpdate(sqlString);
	}
	
	//dont delete- created by Steven Chow
	public void DeleteStudentEnrollment(int studentID, String courseNumber, String sectionNumber) throws SQLException
	{
		Statement stmt = connection.createStatement();

		String sqlString = "DELETE from " + tableName + " WHERE studentID = " + studentID + " and courseNumber = "
				+ AddQuotes(courseNumber) + " and sectionNumber = " + AddQuotes(sectionNumber);
		stmt.executeUpdate(sqlString);
	}
	

	public ArrayList<Integer> GetStudentGPA(String courseNumber, String sectionNumber, String program) throws SQLException
	{
		ArrayList<Integer> list = new ArrayList<Integer>();
		Statement stmt = connection.createStatement();
		String sqlString = "select Student.studentID from Student inner join enrollment on " + 
				" student.studentID = enrollment.studentID where enrollment.courseNumber= " + AddQuotes(courseNumber) + 
				" and enrollment.sectionNumber= " +  AddQuotes(sectionNumber) + " and Student.program = " + AddQuotes(program) 
				+ " order by student.gpa DESC";
		
		ResultSet rs = stmt.executeQuery(sqlString);
		while (rs.next())// check if user exists in databse
		{
			studentID = rs.getInt("studentID");
			list.add(studentID);
		}
		
		return list;
		
	}
}